package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-14
 * Time: 下午4:51
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CwflSfcDao extends SimpleHibernateTemplate {
    /**
     * 财务库存商品分类收发存报表
     *
     * @param gsid
     * @param year
     * @param month
     * @return
     */
    public Map<String, List> cwflSfcList(Long gsid, Integer year, Integer month) {
        if (gsid == null)
            gsid = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_KCSPFL_SFC(?,?,?,?)}");
            call.setLong(1, gsid);
            call.setInt(2, year);
            call.setInt(3, month);
            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(4);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, List> result = new HashMap<String, List>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if ("1".equals(r.get("flag").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 库存商品分类月末结转
     *
     * @param year
     * @param month
     * @return
     */
    public String kcspflSfcYmjz(Integer year, Integer month) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call SAVE_CW_KCSPFL_SFC(?,?,?)}");
            call.setInt(1, year);
            call.setInt(2, month);
            call.registerOutParameter(3, Types.VARCHAR);
            call.execute();
            //====================================
            String errorMsg = call.getString(3);
            return errorMsg;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    /**
     * 采购单统计明细
     *
     * @param gsId
     * @return
     */
    public Map findCgdTjMx(Long gsId, java.util.Date jsStart, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",gg.code \"wzbm\",gg.name \"wzname\",gg.ggxh \"gg\",gg.dw \"dw\",mx.rksl \"rksl\"," +
                " mx.jshsj \"jshsj\",mx.jsbhsj \"jsbhsj\",nvl(mx.jsbhse,0) + nvl(mx.jsse,0) \"hse\",mx.jsse \"se\",mx.jsbhse \"jsbhse\",gys.jc \"gys\"";
        String from = " from t_sys_gg gg" +
                "  left join t_biz_cgdmx mx on(mx.gg_id = gg.id)" +
                "  left join t_biz_cgd cgd on(cgd.id = mx.cgd_id)" +
                "  left join t_sys_gsjg gs on(cgd.sbgs_id = gs.id)" +
                "  left join t_sys_gsjg gys on(gys.id = cgd.gys_id) " +
                "  left join t_biz_jsd jsd on(jsd.id = cgd.cgjs_id)" +
                " where cgd.yjs = 1";
        String where = "";
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsStart != null) {
            where += " and jsd.date_created > :dateStart";
        }
        if (jsEnd != null) {
            where += " and jsd.date_created < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(mx.jsbhse)" + from + where);
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsStart != null) {
            sqlQuery.setDate("dateStart", jsStart);
            countQuery.setDate("dateStart", jsStart);
            sumQuery.setDate("dateStart", jsStart);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", ((BigDecimal) sumQuery.uniqueResult()).floatValue());
        map.put("page", page);
        return map;
    }


    /**
     * 采购单估收统计明细
     *
     * @param gsId
     * @return
     */
    public Map findCgdGsTjMx(Long gsId, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",gg.code \"wzbm\",gg.name \"wzname\",gg.ggxh \"gg\",gg.dw \"dw\",mx.rksl \"rksl\"," +
                " mx.jshsj \"jshsj\",mx.jsbhsj \"jsbhsj\",nvl(mx.jsbhse,0) + nvl(mx.jsse,0) \"hse\",mx.jsse \"se\",mx.jsbhse \"jsbhse\",gys.jc \"gys\"";
        String from = " from t_sys_gg gg" +
                "  left join t_biz_cgdmx mx on(mx.gg_id = gg.id)" +
                "  left join t_biz_cgd cgd on(cgd.id = mx.cgd_id)" +
                "  left join t_sys_gsjg gs on(cgd.sbgs_id = gs.id)" +
                "  left join t_sys_gsjg gys on(gys.id = cgd.gys_id) " +
                " where cgd.yrk = 1 and cgd.yjs <> 1";
        String where = "";
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsEnd != null) {
            where += " and cgd.rksj < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(mx.jsbhse)" + from + where);
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", ((BigDecimal) sumQuery.uniqueResult()).floatValue());
        map.put("page", page);
        return map;
    }

    /**
     * 物流结算明细
     */
    public Map findWljsMx(Long gsId, java.util.Date jsStart, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",sydw.jc \"sydwjc\",cyfy.fylb \"fylb\",fy.jfsl \"jfsl\",fy.je \"je\",fy.kdse \"kdse\",fy.me \"jinge\"";
        String from = " from t_biz_glfy fy" +
                "  left join t_biz_jsd jsd on (fy.wljsd_id = jsd.id)" +
                "  left join t_biz_cgd cgd on (fy.cgd_id = cgd.id)" +
                "  left join t_sys_gsjg gs on(cgd.sbgs_id = gs.id)" +
                "  left join t_sys_gsjg sydw on(sydw.id = fy.sydw_id)" +
                "  left join t_sys_cyfyys cyfy on(fy.cyfy_fylb_id = cyfy.id)" +
                " where jsd.deleted = 0";
        String where = "";
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsStart != null) {
            where += " and jsd.date_created > :dateStart";
        }
        if (jsEnd != null) {
            where += " and cgd.rksj < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(mx.jsbhse)" + from + where);
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsStart != null) {
            sqlQuery.setDate("dateStart", jsStart);
            countQuery.setDate("dateStart", jsStart);
            sumQuery.setDate("dateStart", jsStart);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", ((BigDecimal) sumQuery.uniqueResult()).floatValue());
        map.put("page", page);
        return map;
    }

    /**
     * 物流估收明细
     */
    public Map findWlgsMx(Long gsId, java.util.Date jsEnd, int pageNo, int pageSize) {
        Map<String, Object> map = new HashMap<String, Object>();
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gs.jc \"gsjc\",cgd.cgdh \"cgdh\",sydw.jc \"sydwjc\",cyfy.fylb \"fylb\",fy.jfsl \"jfsl\",fy.je \"je\",fy.kdse \"kdse\",fy.me \"jinge\"";
        String from = " from t_biz_glfy fy" +
                "  left join t_biz_cgd cgd on (fy.cgd_id = cgd.id)" +
                "  left join t_sys_gsjg gs on(cgd.sbgs_id = gs.id)" +
                "  left join t_sys_gsjg sydw on(sydw.id = fy.sydw_id)" +
                "  left join t_sys_cyfyys cyfy on(fy.cyfy_fylb_id = cyfy.id)" +
                "where fy.wljsd_id is null and cgd.yrk = 1 and cgd.yjs <> 1";
        String where = "";
        if (gsId != null) {
            where += " and gs.id = :gsId";
        }
        if (jsEnd != null) {
            where += " and cgd.rksj < :dateEnd";
        }
        SQLQuery sqlQuery = getSession().createSQLQuery(select + from + where + " order by cgd.date_created");
        SQLQuery countQuery = getSession().createSQLQuery("select count(1)" + from + where);
        SQLQuery sumQuery = getSession().createSQLQuery("select sum(mx.jsbhse)" + from + where);
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
            sumQuery.setLong("gsId", gsId);
        }
        if (jsEnd != null) {
            sqlQuery.setDate("dateEnd", jsEnd);
            countQuery.setDate("dateEnd", jsEnd);
            sumQuery.setDate("dateEnd", jsEnd);
        }
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        page.setResult(sqlQuery.list());
        map.put("sum", ((BigDecimal) sumQuery.uniqueResult()).floatValue());
        map.put("page", page);
        return map;
    }
}
